//
//  DB.m
//  tabtest
//
//  Created by clspace on 12-3-21.
//  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.
//

#import "DB.h"

@implementation DB

+ (NSString*) getDBPath
{
    NSString *dbPath  = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"]
                         stringByAppendingPathComponent:@"myhuibao.sqlite"];
    //NSLog(@"path=%@",dbPath);
    return dbPath;	
}
+ (void) addLatestView:(NSDictionary *) dic{
    FMDatabase *db = [FMDatabase databaseWithPath:[self getDBPath]];
    if (![db open]) {
        NSLog(@"Could not open db");
        return ;
    }
    int merchantId = [[dic objectForKey:@"MERCHANT_ID"] intValue];
    //获得系统时间
    NSDate *  viewTime=[NSDate date];
    NSDateFormatter  *dateformatter=[[NSDateFormatter alloc] init];
    [dateformatter setDateFormat:@"YYYY-MM-dd HH:mm"];
    NSString *  vt=[dateformatter stringFromDate:viewTime];
    //[dateformatter setDateFormat:@"YYYY-MM-dd-HH-mm-ss"];
    FMResultSet *rs=[db executeQuery:@"SELECT * FROM latest_view where merchant_id = ? and  merchant_type = ?", [dic objectForKey:@"MERCHANT_ID"],[dic objectForKey:@"TYPEID"]];
//    FMResultSet *rs=[db executeQuery:@"SELECT * FROM latest_view "];
    if ([rs next]) {
        [db executeUpdate:@"update latest_view set merchant_name=?,view_time=? where merchant_id = ? and  merchant_type = ?",
         [dic objectForKey:@"NAME"],vt,[NSNumber numberWithInt:merchantId],[dic objectForKey:@"TYPEID"]];
    }else{
        if([db executeUpdate:@"insert into latest_view (merchant_id,merchant_name,merchant_type,area_name,address,view_time,cuisine,discount,image_path,score)values(?,?,?,?,?,?,?,?,?,?)"
            ,[dic objectForKey:@"MERCHANT_ID"]
            ,[dic objectForKey:@"NAME"]
            ,[dic objectForKey:@"TYPEID"]
            ,[dic objectForKey:@"AREANAME"]==[NSNull null]?@"":[dic objectForKey:@"AREANAME"]
            ,[dic objectForKey:@"ADDRESS"]
            ,vt
            ,[dic objectForKey:@"CUISINE_NAME"]
            ,[dic objectForKey:@"DISCOUNT"]
            ,[dic objectForKey:@"IMAGEPATH"]
            ,[dic objectForKey:@"AVG_SCORE"]
            ]){
            NSLog(@"success");
        }

    }
    [rs close];
    [db close];
}

+ (NSMutableArray *)getLatestViewList:(int) pageIndex{
    NSMutableArray  *recordArray = [[NSMutableArray alloc]init];
    FMDatabase *db = [FMDatabase databaseWithPath:[self getDBPath]];
    if (![db open]) {
        NSLog(@"Could not open db");
        return nil;
    }
    NSLog(@"%d",pageIndex);
    FMResultSet *rs=[db executeQuery:[NSString stringWithFormat:@"SELECT *  FROM latest_view  order by view_time desc limit %d offset %d",pageSize,(pageIndex-1)*pageSize]];
    while ([rs next]) {
        NSMutableDictionary * dic;// = [[NSMutableDictionary alloc]init];
        dic  = [NSMutableDictionary dictionary];
        if ([rs stringForColumn:@"merchant_id"]==nil) {
            continue;
        }
        [dic setObject:[rs stringForColumn:@"merchant_id"] forKey:@"ID"];
        if ([rs stringForColumn:@"merchant_name"] ) 
            [dic setObject:[rs stringForColumn:@"merchant_name"] forKey:@"merchant_name"];
        else
            [dic setObject:@"" forKey:@"merchant_name"];
        [dic setObject:[rs stringForColumn:@"merchant_type"] forKey:@"merchantType"];
        [dic setObject:[rs stringForColumn:@"area_name"]  forKey:@"areaName"];
        [dic setObject:[rs stringForColumn:@"address"] forKey:@"address"];
        [dic setObject:[rs stringForColumn:@"view_time"] forKey:@"view_time"];
        if([rs stringForColumn:@"cuisine"])
            [dic setObject:[rs stringForColumn:@"cuisine"] forKey:@"cuisine"];
        else
            [dic setObject:@"" forKey:@"cuisine"];
        [dic setObject:[rs stringForColumn:@"discount"] forKey:@"discount"];
        if ([rs stringForColumn:@"score"] )
            [dic setObject:[rs stringForColumn:@"score"] forKey:@"score"];
        else
            [dic setObject:@"" forKey:@"score"];

        if ([rs stringForColumn:@"image_path"] )
            [dic setObject:[rs stringForColumn:@"image_path"] forKey:@"image_path"];
        else
            [dic setObject:@"" forKey:@"image_path"];
        [recordArray addObject:dic];
    }
    [rs close];
    [db close];
    return recordArray;
}
+ (void)clearLatestView
{
    FMDatabase *db= [FMDatabase databaseWithPath:[self getDBPath]];
    if (![db open]) {
        //NSLog(@"Could not open db");
        return ;
    }
    [db executeUpdate:@"delete from latest_view"];
    [db close];
}


//初始检查正式数据库是否存在
+ (void)initDatebase
{
    //将资源数据库结构拷贝到文档目录
    //资源DB结构
    NSString *bundPath = [[[NSBundle mainBundle] resourcePath]
                          stringByAppendingPathComponent:@"myhuibao.sqlite"];
    //最终数据库路径
    NSString *dbPath  = [[NSHomeDirectory() stringByAppendingPathComponent:@"Documents"]
                         stringByAppendingPathComponent:@"myhuibao.sqlite"];
    NSLog(@"%@",dbPath);
    //文件管理
    NSFileManager *fm = [NSFileManager defaultManager];
    if(![fm fileExistsAtPath:dbPath])
    {
        //数据库文件不存在,将资源拷贝到
        [fm copyItemAtPath:bundPath toPath:dbPath error:nil];
    }
    //[fm release];
    FMDatabase *db= [FMDatabase databaseWithPath:dbPath];
    if (![db open]) {
        //NSLog(@"Could not open db");
        return ;
    }
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS latest_view (id INTEGER PRIMARY KEY,merchant_id INTEGER ,merchant_name TEXT,merchant_type INTEGER,area_name TEXT,address TEXT,view_time TEXT,cuisine TEXT,discount TEXT,image_path TEXT,score TEXT);"];
    [db close];
}
    
@end
